/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.core.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.core.dao.ReservationDAO;
import com.core.database.Manager;
import com.core.entities.Reservation;
import com.core.exception.DAOException;

/**
 * 
 * @author himanasser
 */
public class ReservationDAOImpl implements ReservationDAO {
		Reservation reservation = new Reservation();
		ArrayList<Reservation> reservationsList = new ArrayList<Reservation>();


	Connection connection = null;
	private static ReservationDAOImpl instance=null;
	public static ReservationDAOImpl getInstance(){
		if(instance==null){
			instance=new ReservationDAOImpl();
		}
		return instance;
	}

	@Override
	public Reservation findByDate(String date) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM reservation WHERE name = '" + date
					+ "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					reservation.setId(rs.getInt(1));
					reservation.setDate(rs.getString(2));
					reservation.setPatient_name(rs.getInt(3));
					reservation.setDoctor_name(rs.getInt(4));
					reservation.setReserver_name(rs.getInt(5));
					reservation.setReservation_type(rs.getInt(6));
					reservation.setReservation_status(rs.getInt(7));
					reservation.setTime_frame(rs.getInt(8));
					reservation.setDecies_case(rs.getInt(9));
					reservation.setCost_id(rs.getInt(10));
					reservation.setDepartment_id(rs.getInt(11));

				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return reservation;
	}

	@Override
	public List<Reservation> findByPatient_name(int patient_name) throws DAOException {

		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM reservation WHERE patient_name = '"
					+ patient_name + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				reservation.setId(rs.getInt(1));
				reservation.setDate(rs.getString(2));
				reservation.setPatient_name(rs.getInt(3));
				reservation.setDoctor_name(rs.getInt(4));
				reservation.setReserver_name(rs.getInt(5));
				reservation.setReservation_type(rs.getInt(6));
				reservation.setReservation_status(rs.getInt(7));
				reservation.setTime_frame(rs.getInt(8));
				reservation.setDecies_case(rs.getInt(9));
				reservation.setCost_id(rs.getInt(10));
				reservation.setDepartment_id(rs.getInt(11));

				reservationsList.add(reservation);

				System.out.println(reservation);
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return reservationsList;
	}

	@Override
	public List<Reservation> findByDoctor_name(int doctor_name) throws DAOException {

		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM reservation WHERE doctor_name = '"
					+ doctor_name + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				reservation.setId(rs.getInt(1));
				reservation.setDate(rs.getString(2));
				reservation.setPatient_name(rs.getInt(3));
				reservation.setDoctor_name(rs.getInt(4));
				reservation.setReserver_name(rs.getInt(5));
				reservation.setReservation_type(rs.getInt(6));
				reservation.setReservation_status(rs.getInt(7));
				reservation.setTime_frame(rs.getInt(8));
				reservation.setDecies_case(rs.getInt(9));
				reservation.setCost_id(rs.getInt(10));
				reservation.setDepartment_id(rs.getInt(11));

				reservationsList.add(reservation);

				System.out.println(reservation);
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return reservationsList;
	}

	@Override
	public List<Reservation> findByReserver_name(int reserver_name)
			throws DAOException {

		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM reservation WHERE reserver_name = '"
					+ reserver_name + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				reservation.setId(rs.getInt(1));
				reservation.setDate(rs.getString(2));
				reservation.setPatient_name(rs.getInt(3));
				reservation.setDoctor_name(rs.getInt(4));
				reservation.setReserver_name(rs.getInt(5));
				reservation.setReservation_type(rs.getInt(6));
				reservation.setReservation_status(rs.getInt(7));
				reservation.setTime_frame(rs.getInt(8));
				reservation.setDecies_case(rs.getInt(9));
				reservation.setCost_id(rs.getInt(10));
				reservation.setDepartment_id(rs.getInt(11));

				reservationsList.add(reservation);
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return reservationsList;
	}

	@Override
	public List<Reservation> findByReservation_type(int reservation_type)
			throws DAOException {

		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM reservation WHERE reservation_type = '"
					+ reservation_type + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				reservation.setId(rs.getInt(1));
				reservation.setDate(rs.getString(2));
				reservation.setPatient_name(rs.getInt(3));
				reservation.setDoctor_name(rs.getInt(4));
				reservation.setReserver_name(rs.getInt(5));
				reservation.setReservation_type(rs.getInt(6));
				reservation.setReservation_status(rs.getInt(7));
				reservation.setTime_frame(rs.getInt(8));
				reservation.setDecies_case(rs.getInt(9));
				reservation.setCost_id(rs.getInt(10));
				reservation.setDepartment_id(rs.getInt(11));

				reservationsList.add(reservation);

			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return reservationsList;
	}

	@Override
	public List<Reservation> findByReservation_status(int reservation_status)
			throws DAOException {

		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM reservation WHERE reservation_status = '"
					+ reservation_status + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				reservation.setId(rs.getInt(1));
				reservation.setDate(rs.getString(2));
				reservation.setPatient_name(rs.getInt(3));
				reservation.setDoctor_name(rs.getInt(4));
				reservation.setReserver_name(rs.getInt(5));
				reservation.setReservation_type(rs.getInt(6));
				reservation.setReservation_status(rs.getInt(7));
				reservation.setTime_frame(rs.getInt(8));
				reservation.setDecies_case(rs.getInt(9));
				reservation.setCost_id(rs.getInt(10));
				reservation.setDepartment_id(rs.getInt(11));

				reservationsList.add(reservation);

			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return reservationsList;
	}

	@Override
	public List<Reservation> findByTime_frame(int time_frame) throws DAOException {

		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM reservation WHERE time_frame = '"
					+ time_frame + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				reservation.setId(rs.getInt(1));
				reservation.setDate(rs.getString(2));
				reservation.setPatient_name(rs.getInt(3));
				reservation.setDoctor_name(rs.getInt(4));
				reservation.setReserver_name(rs.getInt(5));
				reservation.setReservation_type(rs.getInt(6));
				reservation.setReservation_status(rs.getInt(7));
				reservation.setTime_frame(rs.getInt(8));
				reservation.setDecies_case(rs.getInt(9));
				reservation.setCost_id(rs.getInt(10));
				reservation.setDepartment_id(rs.getInt(11));

				reservationsList.add(reservation);

			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return reservationsList;
	}

	@Override
	public List<Reservation> findByDecies_case(int decies_case) throws DAOException {

		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM reservation WHERE decies_case = '"
					+ decies_case + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				reservation.setId(rs.getInt(1));
				reservation.setDate(rs.getString(2));
				reservation.setPatient_name(rs.getInt(3));
				reservation.setDoctor_name(rs.getInt(4));
				reservation.setReserver_name(rs.getInt(5));
				reservation.setReservation_type(rs.getInt(6));
				reservation.setReservation_status(rs.getInt(7));
				reservation.setTime_frame(rs.getInt(8));
				reservation.setDecies_case(rs.getInt(9));
				reservation.setCost_id(rs.getInt(10));
				reservation.setDepartment_id(rs.getInt(11));

				reservationsList.add(reservation);

			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return reservationsList;
	}

	@Override
	public List<Reservation> findByCost_id(int cost_id) throws DAOException {

		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM reservation WHERE cost_id = '"
					+ cost_id + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				reservation.setId(rs.getInt(1));
				reservation.setDate(rs.getString(2));
				reservation.setPatient_name(rs.getInt(3));
				reservation.setDoctor_name(rs.getInt(4));
				reservation.setReserver_name(rs.getInt(5));
				reservation.setReservation_type(rs.getInt(6));
				reservation.setReservation_status(rs.getInt(7));
				reservation.setTime_frame(rs.getInt(8));
				reservation.setDecies_case(rs.getInt(9));
				reservation.setCost_id(rs.getInt(10));
				reservation.setDepartment_id(rs.getInt(11));

				reservationsList.add(reservation);
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return reservationsList;
	}

	@Override
	public List<Reservation> findByDepartment_id(int department_id)
			throws DAOException {

		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM reservation WHERE department_id = '"
					+ department_id + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				reservation.setId(rs.getInt(1));
				reservation.setDate(rs.getString(2));
				reservation.setPatient_name(rs.getInt(3));
				reservation.setDoctor_name(rs.getInt(4));
				reservation.setReserver_name(rs.getInt(5));
				reservation.setReservation_type(rs.getInt(6));
				reservation.setReservation_status(rs.getInt(7));
				reservation.setTime_frame(rs.getInt(8));
				reservation.setDecies_case(rs.getInt(9));
				reservation.setCost_id(rs.getInt(10));
				reservation.setDepartment_id(rs.getInt(11));

				reservationsList.add(reservation);
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return reservationsList;
	}

	@Override
	public void insert(Reservation o) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "INSERT INTO reservation (date, patient_name, doctor_name, reserver_name,"
					+ "reservation_type, reservation_status, time_frame, decies_case, cost_id, department_id ) "
					+ "VALUE (?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setString(1, o.getDate());
			ps.setInt(2, o.getPatient_name());
			ps.setInt(3, o.getDoctor_name());
			ps.setInt(4, o.getReserver_name());
			ps.setInt(5, o.getReservation_type());
			ps.setInt(6, o.getReservation_status());
			ps.setInt(7, o.getTime_frame());
			ps.setInt(8, o.getDecies_case());
			ps.setInt(9, o.getCost_id());
			ps.setInt(10, o.getDepartment_id());

			int result = ps.executeUpdate();
			if (result != 1) {
				throw new SQLException("no row inserted!");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			throw new RuntimeException("Cannot add new Reservation", ex);
		} finally {
			Manager.closeConnection(connection);
		}
	}

	@Override
	public Reservation findById(int id) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM reservation WHERE id = '" + id + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					reservation.setId(rs.getInt(1));
					reservation.setDate(rs.getString(2));
					reservation.setPatient_name(rs.getInt(3));
					reservation.setDoctor_name(rs.getInt(4));
					reservation.setReserver_name(rs.getInt(5));
					reservation.setReservation_type(rs.getInt(6));
					reservation.setReservation_status(rs.getInt(7));
					reservation.setTime_frame(rs.getInt(8));
					reservation.setDecies_case(rs.getInt(9));
					reservation.setCost_id(rs.getInt(10));
					reservation.setDepartment_id(rs.getInt(11));

				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return reservation;
	}

	@Override
	public List<Reservation> findAll() throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = " SELECT * FROM reservation ";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				reservation.setId(rs.getInt(1));
				reservation.setDate(rs.getString(2));
				reservation.setPatient_name(rs.getInt(3));
				reservation.setDoctor_name(rs.getInt(4));
				reservation.setReserver_name(rs.getInt(5));
				reservation.setReservation_type(rs.getInt(6));
				reservation.setReservation_status(rs.getInt(7));
				reservation.setTime_frame(rs.getInt(8));
				reservation.setDecies_case(rs.getInt(9));
				reservation.setCost_id(rs.getInt(10));
				reservation.setDepartment_id(rs.getInt(11));

				reservationsList.add(reservation);

			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return reservationsList;
	}

	@Override
	public void delete(int id) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = " DELETE FROM reservation WHERE id = '" + id + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			int result = ps.executeUpdate();
			if (result != 1) {
				ps.setString(1, reservation.getDate());
				ps.setInt(2, reservation.getPatient_name());
				ps.setInt(3, reservation.getDoctor_name());
				ps.setInt(4, reservation.getReserver_name());
				ps.setInt(5, reservation.getReservation_type());
				ps.setInt(6, reservation.getReservation_status());
				ps.setInt(7, reservation.getTime_frame());
				ps.setInt(8, reservation.getDecies_case());
				ps.setInt(9, reservation.getCost_id());
				ps.setInt(10, reservation.getDepartment_id());
			}

		} catch (SQLException s) {
			s.printStackTrace();

		}
	}

	@Override
	public void update(Reservation o) throws DAOException {

		try {
			connection = Manager.getConnection();
			String sql = "UPDATE reservation SET date = '" + o.getDate()
					+ " ', patient_name = '" + o.getPatient_name()
					+ " ', doctor_name = '" + o.getDoctor_name()
					+ " ', reserver_name = '" + o.getReserver_name()
					+ " ', reservation_type = '" + o.getReservation_type()
					+ " ', reservation_status = '" + o.getReservation_status()
					+ " ', time_frame = '" + o.getTime_frame()
					+ " ', decies_case = '" + o.getDecies_case()
					+ " ', cost_id = '" + o.getCost_id()
					+ " ', department_id = '" + o.getDepartment_id()
					+ "' WHERE id = " + o.getId();
			PreparedStatement ps = connection.prepareStatement(sql);
			int result = ps.executeUpdate(sql);

			if (result != 1) {
				ps.setString(1, o.getDate());
				ps.setInt(2, o.getPatient_name());
				ps.setInt(3, o.getDoctor_name());
				ps.setInt(4, o.getReserver_name());
				ps.setInt(5, o.getReservation_type());
				ps.setInt(6, o.getReservation_status());
				ps.setInt(7, o.getTime_frame());
				ps.setInt(8, o.getDecies_case());
				ps.setInt(9, o.getCost_id());
				ps.setInt(10, o.getDepartment_id());
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
	}
}
